package veterinaria.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import veterinaria.excepcion.DAOExcepcion;
import veterinaria.modelo.Persona;
import veterinaria.util.ConexionBD;

public class PersonaDAO extends BaseDAO {

    public Collection<Persona> buscarPorNombre(String pNom) throws DAOExcepcion {
        String cSql="Select PerCodigo,PerNombre,PerDomicilio,PerTelefono,PerCelular,PerCorreo,PerImagen From MPersona Where PerNombre Like ? And PerFlg<>'E'";
        Collection<Persona> cLis=new ArrayList<Persona>();
        Connection cCon=null;
        PreparedStatement cCom=null;
        ResultSet cRst=null;
        try {
            cCon=ConexionBD.obtenerConexion();
            cCom=cCon.prepareStatement(cSql);
            cCom.setString(1, "%" + pNom + "%");
            cRst=cCom.executeQuery();
            while (cRst.next()) {
                Persona cReg = new Persona();
                cReg.setPerCodigo(cRst.getInt("PerCodigo"));
                cReg.setPerNombre(cRst.getString("PerNombre"));
                cReg.setPerDomicilio(cRst.getString("PerDomicilio"));
                cReg.setPerTelefono(cRst.getString("PerTelefono"));
                cReg.setPerCelular(cRst.getString("PerCelular"));
                cReg.setPerCorreo(cRst.getString("PerCorreo"));
                cReg.setPerImagen(cRst.getString("PerImagen"));
                cLis.add(cReg);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
            
        } finally {
            this.cerrarResultSet(cRst);
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
        System.out.println(cLis.size());
        return cLis;
    }

    public Persona insertar(Persona pReg) throws DAOExcepcion {
        String cSql = "Insert Into MPersona(PerNombre,PerDomicilio,PerTelefono,PerCelular,PerCorreo,PerImagen,PerFlg) Values (?,?,?,?,?,?,?)";
        Connection cCon = null;
        PreparedStatement cCom = null;
        ResultSet cRst = null;
        try {
            cCon = ConexionBD.obtenerConexion();
            cCom = cCon.prepareStatement(cSql);
            cCom.setString(1, pReg.getPerNombre());
            cCom.setString(2, pReg.getPerDomicilio());
            cCom.setString(3, pReg.getPerTelefono());
            cCom.setString(4, pReg.getPerCelular());
            cCom.setString(5, pReg.getPerCorreo());
            cCom.setString(6, pReg.getPerImagen());
            cCom.setString(7, "A");           
            int nroErr = cCom.executeUpdate();
            if (nroErr != 1) {
                throw new SQLException("No se pudo insertar");
            }
            int newCod = 0;
            cSql="Select Last_Insert_id()";
            cCom=cCon.prepareStatement(cSql);
            cRst=cCom.executeQuery();
            if (cRst.next()) {
                newCod=cRst.getInt(1);
            }
            pReg.setPerCodigo(newCod);
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(cRst);
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
        return pReg;
    }

    public Persona obtener(int pCod) throws DAOExcepcion {
        Persona cReg=new Persona();
        Connection cCon=null;
        PreparedStatement cCom=null;
        ResultSet cRst=null;
        try {
            String cSql = "Select PerCodigo,PerNombre,PerDomicilio,PerTelefono,PerCelular,PerCorreo,PerImagen From MPersona Where PerCodigo=? And PerFlg<>'E' Order By 1";
            cCon = ConexionBD.obtenerConexion();
            cCom = cCon.prepareStatement(cSql);
            cCom.setInt(1, pCod);
            cRst = cCom.executeQuery();
            if (cRst.next()) {
                cReg.setPerCodigo(cRst.getInt(1));
                cReg.setPerNombre(cRst.getString(2));
                cReg.setPerDomicilio(cRst.getString(3));
                cReg.setPerTelefono(cRst.getString(4));
                cReg.setPerCelular(cRst.getString(5));
                cReg.setPerCorreo(cRst.getString(6));
                cReg.setPerImagen(cRst.getString(7));
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(cRst);
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
        return cReg;
    }

    public void eliminar(int pCod) throws DAOExcepcion {
        String cSql = "UpDate MPersona Set PerFlg=? Where PerCodigo=? And PerFlg<>'E'";
        Connection cCon = null;
        PreparedStatement cCom = null;
        try {
            cCon = ConexionBD.obtenerConexion();
            cCom = cCon.prepareStatement(cSql);
            cCom.setString(1,"E");
            cCom.setInt(2, pCod);
            int i = cCom.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo eliminar");
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
    }

    public Persona actualizar(Persona pReg) throws DAOExcepcion {
        String cSql="UpDate MPersona Set PerNombre=?,PerDomicilio=?,PerTelefono=?,PerCelular=?,PerCorreo=?,PerImagen=?,PerFlg=? Where PerCodigo=? And PerFlg<>'E'";
        Connection cCon=null;
        PreparedStatement cCom=null;
        try {
            cCon=ConexionBD.obtenerConexion();
            cCom=cCon.prepareStatement(cSql);
            cCom.setString(1, pReg.getPerNombre());
            cCom.setString(2, pReg.getPerDomicilio());
            cCom.setString(3, pReg.getPerTelefono());
            cCom.setString(4, pReg.getPerCelular());
            cCom.setString(5, pReg.getPerCorreo());
            cCom.setString(6, pReg.getPerImagen());
            cCom.setString(7, "M");
            cCom.setInt(8, pReg.getPerCodigo());
            int i = cCom.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo actualizar");
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
        return pReg;
    }

    public Collection<Persona> listar() throws DAOExcepcion {
        Collection<Persona> c = new ArrayList<Persona>();
        Connection cCon = null;
        PreparedStatement cCom = null;
        ResultSet cRst = null;
        try {
            cCon = ConexionBD.obtenerConexion();
            String cSql = "Select PerCodigo,PerNombre,PerDomicilio,PerTelefono,PerCelular,PerCorreo,PerImagen From MPersona Where PerFlg<>'E' Order By 1";
            cCom = cCon.prepareStatement(cSql);
            cRst = cCom.executeQuery();
            while (cRst.next()) {
                Persona cReg = new Persona();
                cReg.setPerCodigo(cRst.getInt("PerCodigo"));
                cReg.setPerNombre(cRst.getString("PerNombre"));
                cReg.setPerDomicilio(cRst.getString("PerDomicilio"));
                cReg.setPerTelefono(cRst.getString("PerTelefono"));
                cReg.setPerCelular(cRst.getString("PerCelular"));
                cReg.setPerCorreo(cRst.getString("PerCorreo"));
                cReg.setPerImagen(cRst.getString("PerImagen"));
                c.add(cReg);
            }

        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(cRst);
            this.cerrarStatement(cCom);
            this.cerrarConexion(cCon);
        }
        return c;
    }

    public String busNomPersona(String pNom) throws DAOExcepcion {
		Persona cReg = new Persona();
		Connection cCon = null;
		PreparedStatement cCom = null;
		ResultSet cRst = null;
		try {
			String query = "Select PerCodigo,PerNombre From MPersona Where PerNombre=? And PerFlg<>'E'";
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(query);
			cCom.setString(1, pNom);
			cRst=cCom.executeQuery();
			if (cRst.next()) {
				cReg.setPerCodigo(cRst.getInt(1));
				cReg.setPerNombre(cRst.getString(2));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return cReg.getPerNombre();
	}
    
    public int busCodPersona(String pNom) throws DAOExcepcion {
		Persona cReg = new Persona();
		Connection cCon = null;
		PreparedStatement cCom = null;
		ResultSet cRst = null;
		try {
			String query = "Select PerCodigo From MPersona Where PerNombre=? And PerFlg<>'E'";
			cCon=ConexionBD.obtenerConexion();
			cCom=cCon.prepareStatement(query);
			cCom.setString(1, pNom);
			cRst=cCom.executeQuery();
			if (cRst.next()) {
				cReg.setPerCodigo(cRst.getInt(1));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(cRst);
			this.cerrarStatement(cCom);
			this.cerrarConexion(cCon);
		}
		return cReg.getPerCodigo();
	}        
}

